package com.opennetexam.manager.sqlprovider;

import com.opennetexam.manager.enums.ExamManagerState;
import com.opennetexam.manager.enums.ExamPlanSysState;
import com.opennetexam.manager.enums.ExamResultUploadState;
import org.apache.log4j.Logger;

import java.util.HashMap;
import java.util.Map;

/**
 * @author 奶茶
 * Created by on 2017/12/8.
 */
public class ExamReportProvider {

    private static final Logger logger = Logger.getLogger(ExamReportProvider.class);

    public String findExamReportListBySomeWhere(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder("");
        Object appId = parameter.get("appId");
        Object planId = parameter.get("planId");
        Object roomId = parameter.get("roomId");
        Object sceneId = parameter.get("sceneId");
        Object subjectId = parameter.get("subjectId");
        Object reportState = parameter.get("reportState");

        sb.append("SELECT DISTINCT dataPlan.ExamPlanName,dataRoom.ExamRoomName,dataScenes.ExamSceneName,dataSubject.SubjectName examSubjectName,dataReport.IfUpload ExamReportState,dataReport.IfUpload,dataReport.ExamResultState,dataSubject.ExamSubjectID,dataScenes.ExamSceneID,dataRoom.ExamRoomID,dataReport.ExamReportID,dataScenes.PlanBeginTime");
        sb.append(" FROM ( SELECT * FROM ExamReport WHERE 1=1 ");
        if(appId != null){
            sb.append(" AND AppID = \'").append(String.valueOf(appId)).append("\'");
        }
        if(planId != null){
            sb.append(" AND ExamPlanInfoID = \'").append(planId).append("\' ");
        }
        if(roomId != null){
            sb.append(" AND ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(sceneId != null){
            sb.append(" AND ExamSceneID = \'").append(sceneId).append("\' ");
        }
        if(subjectId != null){
            sb.append(" AND ExamSubjectID = \'").append(subjectId).append("\' ");
        }
        if(reportState != null){
            sb.append(" AND ExamReportState = ").append(reportState);
        }
        sb.append(" ) AS dataReport");

        sb.append(" JOIN ExamPlanInfo AS dataPlan ON dataReport.ExamPlanInfoID = dataPlan.ExamPlanInfoID");
        sb.append(" JOIN ExamRoom AS dataRoom ON dataReport.ExamRoomID = dataRoom.ExamRoomID");
        sb.append(" JOIN ExamScenes AS dataScenes ON dataReport.ExamSceneID = dataScenes.ExamSceneID");
        sb.append(" JOIN ExamSubject AS dataSubject ON dataReport.ExamSubjectID = dataSubject.ExamSubjectID");
        sb.append(" WHERE dataPlan.SysState = ").append(ExamPlanSysState.TestPaperCreated.getPrefix());
        sb.append(" AND dataReport.AppID = dataPlan.AppID");
        sb.append(" AND dataRoom.AppID = dataPlan.AppID AND dataRoom.ExamSiteID = dataPlan.ExamSiteID");
        sb.append(" AND dataScenes.AppID = dataPlan.AppID AND dataScenes.ExamPlanInfoID = dataPlan.ExamPlanInfoID");
        sb.append(" AND dataSubject.AppID = dataPlan.AppID AND dataSubject.ExamPlanInfoID = dataPlan.ExamPlanInfoID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public String findAbsenceOrAllCountList(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder("");

        Object appId = parameter.get("appId");
        Object planId = parameter.get("planId");
        Object roomId = parameter.get("roomId");
        Object sceneId = parameter.get("sceneId");
        Object subjectId = parameter.get("subjectId");
        Object sysState = parameter.get("sysState");

        sb.append("select AppID,ExamPlanInfoID,ExamSubjectID,ExamSceneID,ExamRoomID from ExamingMonitor ");
        sb.append("where AppID = \'").append(appId).append("\'").append(" and ExamPlanInfoID = \'").append(planId).append("\' ");
        if(roomId != null){
            sb.append(" and ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(sceneId != null){
            sb.append(" and ExamSceneID = \'").append(sceneId).append("\' ");
        }
        if(subjectId != null){
            sb.append(" and ExamSubjectID = \'").append(subjectId).append("\' ");
        }
        if(sysState != null){
            sb.append(" and SysState = ").append(sysState);
        }
        sb.append(" group by ExamineeID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public String findExamingMonitorList(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder("");

        Object appId = parameter.get("appId");
        Object planId = parameter.get("planId");
        Object subjectId = parameter.get("subjectId");
        Object roomId = parameter.get("roomId");
        Object sceneId = parameter.get("sceneId");
        Object siteId = parameter.get("siteId");


        sb.append("SELECT dataSub.SubjectName ExamSubjectName,dataSub.SubjectCode subjectCode,dataArrange.ExamineeArrangeID ID,dataPaper.ExamPaperID examPaperID,datali.SubmitType,dataSub.ExamSubjectID,dataExaminee.ExamineeID,datali.LoginTime,datali.ExamStartTime RelBeginTime,dataExaminee.ExamineeName,dataExaminee.ExamCardNo,datali.ExamEndTime,datali.SysState,datali.DelayMinute,dataPaper.ExamPaperInfoID ExamineeExamPaperID,dataPaper.ExamPaperContent ExamineeResult,dataPaper.ExamPaperID,dataExamPaper.PaperID sourceExamPaperID,dataScene.ExamSceneID,dataScene.ExamSceneName,dataRoom.ExamRoomID,dataRoom.ExamRoomName,datali.DelayMinute DelayedTime,dataScene.PlanBeginTime,datali.IDCardResult Register,datali.IDCardReason Remark,datali.IDCardTime RegisterTime,dataPlan.ExamPlanName ExamPlanInfoName,dataPlan.DeploymentMode deploymentMode,dataSite.ExamSiteName,dataExaminee.CertificateNo ");
        sb.append("FROM ( SELECT * FROM ExamingMonitor WHERE AppID = \'").append(appId).append("\'").append(" AND ExamPlanInfoID = \'").append(planId).append("\' AND ExamSubjectID = \'").append(subjectId).append("\' ");
        if(roomId != null){
            sb.append(" AND ExamRoomID = \'").append(roomId).append("\' ");
        }
        if(sceneId != null){
            sb.append(" AND ExamSceneID = \'").append(sceneId).append("\' ");
        }
        sb.append(" ) AS datali");

        sb.append(" JOIN ExamineeArrange AS dataArrange ON datali.AppID = dataArrange.AppID");
        sb.append(" JOIN Examinee AS dataExaminee ON dataArrange.ExamineeID = dataExaminee.ExamineeID");
        sb.append(" JOIN ExamPlanInfo AS dataPlan ON dataArrange.ExamPlanInfoID = dataPlan.ExamPlanInfoID");
        sb.append(" JOIN ExamRoom AS dataRoom ON dataArrange.ExamRoomID = dataRoom.ExamRoomID");
        sb.append(" JOIN ExamScenes AS dataScene ON datali.ExamSceneID = dataScene.ExamSceneID");
        sb.append(" JOIN ExamSite AS dataSite ON dataSite.ExamSiteID = \'").append(siteId).append("\' ");
        sb.append(" JOIN ExamSubject AS dataSub ON dataArrange.ExamSubjectID = dataSub.ExamSubjectID");
        sb.append(" JOIN ExamineeExamPaper AS dataPaper ON dataArrange.ExamineeID = dataPaper.ExamineeID");
        sb.append(" JOIN ExamPaper AS dataExamPaper ON dataExamPaper.ExamPaperID = dataPaper.ExamPaperID");
        sb.append(" WHERE dataArrange.ExamPlanInfoID = datali.ExamPlanInfoID AND dataArrange.ExamSubjectID = datali.ExamSubjectID AND dataArrange.ExamineeID = datali.ExamineeID");
        sb.append(" AND dataExaminee.AppID = datali.AppID AND dataExaminee.ExamSiteID = \'").append(siteId).append("\' AND dataExaminee.ExamPlanInfoID = datali.ExamPlanInfoID AND dataExaminee.AccountType = 0");
        sb.append(" AND dataPlan.AppID = datali.AppID");
        sb.append(" AND dataRoom.AppID = datali.AppID AND dataRoom.ExamSiteID = \'").append(siteId).append("\' ");
        sb.append(" AND dataScene.AppID = datali.AppID AND dataScene.ExamPlanInfoID = datali.ExamPlanInfoID");
        sb.append(" AND dataSub.AppID = datali.AppID AND dataSub.ExamPlanInfoID = datali.ExamPlanInfoID");
        sb.append(" AND dataPlan.ExamPlanInfoID = dataPaper.ExamPlanInfoID AND dataPlan.AppID = dataPaper.AppID AND dataSub.ExamSubjectID = dataPaper.ExamSubjectID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public String findFreeExamReportList(Map<String,Object> parameter){
        StringBuilder sb = new StringBuilder("");
        Object appId = parameter.get("appId");
        Object planId = parameter.get("planId");
        Object subjectId = parameter.get("subjectId");
        Object siteId = parameter.get("siteId");
        Object examCardNo = parameter.get("examCardNo");
        Object examineeName = parameter.get("examineeName");

        sb.append("SELECT dataArrange.ExamineeArrangeID ID,dataArrange.ExamineeName,dataExaminee.ExamCardNo,datali.SysState,datali.DelayMinute,datali.ExamEndTime,dataArrange.ExamineeID,datali.LoginTime,datali.ExamStartTime RelBeginTime,datali.ExamSubjectID,datali.DelayMinute DelayedTime,datali.ExamSceneID,datali.ExamRoomID,dataSubject.SubjectName,dataExaminee.AccountType ");
        sb.append("FROM ( SELECT * FROM ExamingMonitor WHERE AppID = \'").append(appId).append("\' AND ExamPlanInfoID = \'").append(planId).append("\'");
        if(subjectId != null){
            sb.append(" AND ExamSubjectID = \'").append(subjectId).append("\' ");
        }
        sb.append(" ) AS datali");

        sb.append(" JOIN ExamineeArrange AS dataArrange ON datali.AppID = dataArrange.AppID");
        sb.append(" JOIN Examinee AS dataExaminee ON dataArrange.ExamineeID = dataExaminee.ExamineeID");
        sb.append(" JOIN ExamSubject AS dataSubject ON dataArrange.ExamSubjectID = dataSubject.ExamSubjectID");
        sb.append(" WHERE dataArrange.ExamPlanInfoID = datali.ExamPlanInfoID AND dataArrange.ExamSubjectID = datali.ExamSubjectID AND dataArrange.ExamineeID = datali.ExamineeID");
        sb.append(" AND dataExaminee.ExamPlanInfoID = dataArrange.ExamPlanInfoID AND dataExaminee.AppID = dataArrange.AppID AND dataExaminee.ExamSiteID = \'").append(siteId).append("\' ");
        sb.append(" AND dataSubject.AppID = dataArrange.AppID AND dataSubject.ExamPlanInfoID = dataArrange.ExamPlanInfoID");
        if(examCardNo != null){
            sb.append(" AND dataExaminee.ExamCardNo LIKE \'%").append(examCardNo).append("%\'");
        }
        if(examineeName != null){
            sb.append(" AND dataExaminee.ExamineeName LIKE \'%").append(examineeName).append("%\'");
        }
        sb.append(" GROUP BY dataArrange.ExamSubjectID,dataArrange.ExamineeID");

        String sql = sb.toString();
        logger.info(sql);
        return sql;
    }

    public String findFreeExamSubjectReportList(Map<String,Object> parameter){
        StringBuilder sql = new StringBuilder("");
        String examPlanID = parameter.get("examPlanID").toString();
        Object upload = parameter.get("upload");
        Object examResultStatus = parameter.get("examResultStatus");

        sql.append("SELECT\n" +
                "\texamPlan.ExamPlanInfoID,\n" +
                "\texamPlan.DeploymentMode,\n" +
                "\texamPlan.ExamMode,\n" +
                "\texamSubject.ExamSubjectID,\n" +
                "\texamSubject.SubjectName,\n" +
                "\tIFNULL(\n" +
                "\t\tfreeExamReport.ExamReportID,\n" +
                "\t\t''\n" +
                "\t) ExamReportID,\n" +
                "\tIFNULL(\n" +
                "\t\tfreeExamReport.ExamResult,\n" +
                "\t\t''\n" +
                "\t) examResult,\n" +
                "\tIFNULL(freeExamReport.IfUpload, '') upload,\n" +
                "\tIFNULL(\n" +
                "\t\tfreeExamReport.CreateTime,\n" +
                "\t\t''\n" +
                "\t) createTime,\n" +
                "\tIFNULL(\n" +
                "\t\tfreeExamReport.UploadTime,\n" +
                "\t\t''\n" +
                "\t) uploadTime,\n" +
                "\tCOUNT(1) examinerCount\n" +
                "FROM\n" +
                "\tExamPlanInfo examPlan ");
        sql.append("JOIN ExamSubject examSubject ON examSubject.ExamPlanInfoID = examPlan.ExamPlanInfoID\n" +
                "JOIN ExamingMonitor examingMonitor ON examingMonitor.ExamPlanInfoID = examPlan.ExamPlanInfoID\n" +
                "LEFT JOIN FreeExamReport freeExamReport ON freeExamReport.ExamPlanInfoID = examPlan.ExamPlanInfoID\n" +
                "AND freeExamReport.ExamSubjectID = examSubject.ExamSubjectID ");
        sql.append("WHERE examPlan.ExamPlanInfoID = \'").append(examPlanID).append("\'");
        sql.append(" AND examingMonitor.ExamSubjectID = examSubject.ExamSubjectID ");
        if(upload != null){
            if(upload.equals(ExamResultUploadState.DontUpload.getPrefix())){
                sql.append(" AND freeExamReport.IfUpload is null ");
            }else{
                sql.append(" AND freeExamReport.IfUpload = ").append(upload);
            }
        }
        if(examResultStatus != null){
            sql.append(" AND freeExamReport.ExamResult != '' ");
        }
        sql.append(" GROUP BY examSubject.ExamSubjectID");

        logger.info(sql);
        return sql.toString();
    }

    public static void main(String[] args) {
        Map<String,Object> map = new HashMap<>();
        map.put("appId","1");
        map.put("planId",98);
        map.put("roomId",750);
        map.put("sceneId",195);
        map.put("subjectId",285);
        map.put("reportState",0);
        map.put("siteId",590);
        map.put("examCardNo",1);
        map.put("examineeName",1);

//        findFreeExamReportList(map);
    }
}
